// 分类相关业务逻辑

// 引入数据库执行的工具函数
import { executeSql } from '../util/db.js'

// 获取所有分类(已取消where state = 1、and state = 1的条件)
export const selectAllCategories = function(){
    let sql = "select * from categories order by id";
    return executeSql(sql);
}

// 根据ID获取分类信息
export const selectCategoryById = function(id){
    let sql = `select * from categories where id = ?`;
    return executeSql(sql, [parseInt(id)]);
}

//------------------管理后台相关----------------------
// 添加分类
export const insertCategory = function(name, slug){
    let sql = `insert into categories (name, slug) values (?, ?)`;
    return executeSql(sql, [name, slug]);
}

// 更新分类
export const updateCategory = function(id, name, slug){
    let sql = `update categories set name = ?, slug = ? where id = ?`;
    return executeSql(sql, [name, slug, parseInt(id)]);
}

// 删除分类
export const deleteCategory = function(id){
    let sql = `delete from categories where id = ?`;
    return executeSql(sql, [parseInt(id)]);
}

// 获取分类总数
export const selectCategoriesCount = function(){
    let sql = "select count(id) count from categories";
    return executeSql(sql);
}

// 获取各分类的文章数量统计
export const selectCategoryArticleCount = function(){
    let sql = `select c.name, count(a.id) as articleCount from categories c left join articles a on c.id = a.categoryId and a.isDelete = 0 group by c.id, c.name order by c.id`;
    return executeSql(sql);
}

// 检查分类名称是否重复
export const checkCategoryNameExists = function(name){
    let sql = `select id from categories where name = ?`;
    return executeSql(sql, [name]);
}

// 检查分类别名是否重复
export const checkCategorySlugExists = function(slug){
    let sql = `select id from categories where slug = ?`;
    return executeSql(sql, [slug]);
}

// 检查分类名称是否重复（排除指定ID）
export const checkCategoryNameExistsExcludeId = function(name, excludeId){
    let sql = `select id from categories where name = ? and id != ?`;
    return executeSql(sql, [name, parseInt(excludeId)]);
}

// 检查分类别名是否重复（排除指定ID）
export const checkCategorySlugExistsExcludeId = function(slug, excludeId){
    let sql = `select id from categories where slug = ? and id != ?`;
    return executeSql(sql, [slug, parseInt(excludeId)]);
}
